﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace TestMetrics.Console
{
    public class AzureController
    {
        Setting setting;

        private const string ETLHistoryTableName = "ETLHistoryAzure";
        private const string BugListTableName = "BugListAzure";
        private const string NumOfCreatedTCATableName = "NumOfCreatedTCAzure";
        private const string NumOfExecutedTCTableName = "NumOfExecutedTCAzure";
        private const string TestAutomationTableName = "TestAutomationAzure";
        private const string ReleaseInfoTableName = "ReleaseInfoAzure";
        private const string ReleaseDetailInfoTableName = "ReleaseDetailInfoAzure";
        private const string TestCaseInCITableName = "TestCaseInCIAzure";
        private const string GlobalTestCasePassRateTableName = "GlobalTestCasePassRateAzure";
        private const string GlobalTestCaseAutomationTableName = "GlobalTestCaseAutomationAzure";
        private const string GlobalDRETableName = "GlobalDREAzure";

        private const string TestCaseListTableName = "TestCaseListAzure";

        public AzureController()
        {
            //init setting
            setting = new Setting();
        }

        private bool CreateETLHistoryAzure()
        {
            // BugList
            string sql =
                    "IF OBJECT_ID('" + setting.StrSchema + "." + ETLHistoryTableName + "') IS NULL " +
                    "CREATE TABLE " + setting.StrSchema + "." + ETLHistoryTableName + "( " +
                    "ID int NOT NULL, " +
                    "TableName nvarchar(100) NULL, " +
                    "LastUpdateDT datetime2(7) NULL, )" + Environment.NewLine +
                    "if not exists(select 1 from sys.indexes where object_id=OBJECT_ID('" + setting.StrSchema + "." + ETLHistoryTableName + "') and name='PK_" + ETLHistoryTableName + "_ID')" + Environment.NewLine +
                    "create clustered index PK_" + ETLHistoryTableName + "_ID ON " + setting.StrSchema + "." + ETLHistoryTableName + "(ID);" + Environment.NewLine;
            try
            {
                DBHelper.ExecuteNonQuery(setting.TargetConnectionString, CommandType.Text, sql, null);
                return true;
            }
            catch (Exception ex)
            {
                IOHelper.Log("CreateETLHistoryAzure ====> " + ex.ToString());
                throw ex;
            }
        }

        private bool CreateBugListAzure()
        {
            
            // BugList
            string sql =
                    "IF OBJECT_ID('" + setting.StrSchema + "." + BugListTableName + "') IS NULL " +
                    "CREATE TABLE " + setting.StrSchema + "." + BugListTableName + "( " +
                    "ID int NOT NULL, " +
                    "ItemID int NOT NULL, " +
                    "ProjectName nvarchar(200) NULL, " +
                    "AssignedTo nvarchar(200) NULL, " +
                    "State nvarchar(200)  NULL, " +
                    "Severity nvarchar(200) NULL, " +
                    "Priority nvarchar(200) NULL, " +
                    "FoundInEnvironment nvarchar(200) NULL, " +
                    "CreatedDate datetime NULL, " +
                    "ResolvedDate datetime NULL," +
                    "ClosedDate datetime NULL," +
                    "ProjectFriendName nvarchar(200) NULL," +
                    "BugType nvarchar(200) NULL," +
                    "CreatedBy nvarchar(200) NULL," +
                    "ResolvedBy nvarchar(200) NULL);" + Environment.NewLine +
                    "if not exists(select 1 from sys.indexes where object_id=OBJECT_ID('" + setting.StrSchema + "." + BugListTableName + "') and name='PK_" + BugListTableName + "_ID')" + Environment.NewLine +
                    "create clustered index PK_" + BugListTableName + "_ID ON " + setting.StrSchema + "." + BugListTableName + "(ItemID);" + Environment.NewLine;
            try
            {
                DBHelper.ExecuteNonQuery(setting.TargetConnectionString, CommandType.Text, sql, null);
                return true;
            }
            catch (Exception ex)
            {
                IOHelper.Log("CreateBugListAzure ====> " + ex.ToString());
                throw ex;
            }
        }
        private bool CreateNumOfCreatedTCAzure()
        {

            // NumOfCreatedTC
            string sql =
                    "IF OBJECT_ID('" + setting.StrSchema + "." + NumOfCreatedTCATableName + "') IS NULL " +
                    "CREATE TABLE " + setting.StrSchema + "." + NumOfCreatedTCATableName + "( " +
                    "[ID] int identity(1,1) not NULL, " +
                    "[Application] nvarchar(200) NULL, " +
                    "[CreatedDate] nvarchar(200) NULL, " +
                    "[NumCreatedTC] int NULL); " + Environment.NewLine+

            "if not exists(select 1 from sys.indexes where object_id=OBJECT_ID('" + setting.StrSchema + "." + NumOfCreatedTCATableName + "') and name='PK_" + NumOfCreatedTCATableName + "_ID')" + Environment.NewLine +
            "create clustered index PK_" + NumOfCreatedTCATableName + "_ID ON " + setting.StrSchema + "." + NumOfCreatedTCATableName + "(ID);" + Environment.NewLine;
            try
            {
                DBHelper.ExecuteNonQuery(setting.TargetConnectionString, CommandType.Text, sql, null);
                return true;
            }
            catch (Exception ex)
            {
                IOHelper.Log("CreateNumOfCreatedTCAzure ====> " + ex.ToString());
                throw ex;
            }
        }

        private bool CreateNumOfExecutedTCAzure()
        {

            // NumOfExecutedTC
            string sql =
                    "IF OBJECT_ID('" + setting.StrSchema + "." + NumOfExecutedTCTableName + "') IS NULL " +
                    "CREATE TABLE " + setting.StrSchema + "." + NumOfExecutedTCTableName + "( " +
                    "[ID] int identity(1,1) not NULL, " +
                    "[Application] nvarchar(200) NULL, " +
                    "[ExecutedStartDt] nvarchar(200) NULL, " +
                    "[NumExecutedTC] int NULL, " +
                    "[NumPassed] int NULL, " +
                    "[NumFailed] int NULL); " + Environment.NewLine +

            "if not exists(select 1 from sys.indexes where object_id=OBJECT_ID('" + setting.StrSchema + "." + NumOfExecutedTCTableName + "') and name='PK_" + NumOfExecutedTCTableName + "_ID')" + Environment.NewLine +
            "create clustered index PK_" + NumOfExecutedTCTableName + "_ID ON " + setting.StrSchema + "." + NumOfExecutedTCTableName + "(ID);" + Environment.NewLine;
            try
            {
                DBHelper.ExecuteNonQuery(setting.TargetConnectionString, CommandType.Text, sql, null);
                return true;
            }
            catch (Exception ex)
            {
                IOHelper.Log("CreateNumOfExecutedTCAzure ====> " + ex.ToString());
                throw ex;
            }
        }

        private bool CreateTestAutomationAzure()
        {

            // TestAutomation
            string sql =
                    "IF OBJECT_ID('" + setting.StrSchema + "." + TestAutomationTableName + "') IS NULL " +
                    "CREATE TABLE " + setting.StrSchema + "." + TestAutomationTableName + "( " +
                    "[ID] int identity(1,1) not NULL, " +
                    "[Application] nvarchar(200) NULL, " +
                    "[CreatedDate] nvarchar(200) NULL, " +
                    "[TotalTestCases] int NULL, " +
                    "[Automatable] int NULL, " +
                    "[CannotAutomated] int NULL, " +
                    "[AutomatableNotReady] int NULL, " +
                    "[AlreadyAutomated] int NULL, " +
                    "[Gap] int NULL, " +
                    "[RateAlreadyAutomated] int NULL, " +
                    "[RateAutomatable] int NULL, " +
                    "[LinesOfAutomationCode] int NULL); " + Environment.NewLine +

            "if not exists(select 1 from sys.indexes where object_id=OBJECT_ID('" + setting.StrSchema + "." + TestAutomationTableName + "') and name='PK_" + TestAutomationTableName + "_ID')" + Environment.NewLine +
            "create clustered index PK_" + TestAutomationTableName + "_ID ON " + setting.StrSchema + "." + TestAutomationTableName + "(ID);" + Environment.NewLine;
            try
            {
                DBHelper.ExecuteNonQuery(setting.TargetConnectionString, CommandType.Text, sql, null);
                return true;
            }
            catch (Exception ex)
            {
                IOHelper.Log("CreateTestAutomationAzure ====> " + ex.ToString());
                throw ex;
            }
        }

        private bool CreateTestCaseListAzure()
        {
            // TestCase List
            string sql =
                    "IF OBJECT_ID('" + setting.StrSchema + "." + TestCaseListTableName + "') IS NULL " +
                    "CREATE TABLE " + setting.StrSchema + "." + TestCaseListTableName + "( " +
                    "ID int NOT NULL, " +
                    "ItemID int NOT NULL, " +
                    "ProjectName nvarchar(200) NULL, " +
                    "AreaPath nvarchar(200) NULL, " +
                    "IterationPath nvarchar(200) NULL, " +
                    "State nvarchar(200)  NULL, " +
                    "Priority nvarchar(200) NULL, " +
                    "AutomationStatus nvarchar(200) NULL, " +
                    "AssignedTo nvarchar(200) NULL, " +
                    "CreatedBy nvarchar(200) NULL," +
                    "CreatedDate datetime NULL, " +
                    "ClosedBy nvarchar(200) NULL," +
                    "Class nvarchar(200) NULL," +
                    "ClosedDate datetime NULL," +
                    "Frequency nvarchar(200) NULL, " +
                    "ProjectFriendName nvarchar(200) NULL)" + Environment.NewLine +
                    "if not exists(select 1 from sys.indexes where object_id=OBJECT_ID('" + setting.StrSchema + "." + TestCaseListTableName + "') and name='PK_" + TestCaseListTableName + "_ID')" + Environment.NewLine +
                    "create clustered index PK_" + TestCaseListTableName + "_ID ON " + setting.StrSchema + "." + TestCaseListTableName + "(ItemID);" + Environment.NewLine;
            try
            {
                DBHelper.ExecuteNonQuery(setting.TargetConnectionString, CommandType.Text, sql, null);
                return true;
            }
            catch (Exception ex)
            {
                IOHelper.Log("CreateTestCaseListAzure ====> " + ex.ToString());
                throw ex;
            }
        }

        private bool CreateReleaseInfoAzure()
        {
            // Release Info
            string sql =
                    "IF OBJECT_ID('" + setting.StrSchema + "." + ReleaseInfoTableName + "') IS NULL " +
                    "CREATE TABLE " + setting.StrSchema + "." + ReleaseInfoTableName + "( " +
                    "[ID] int identity(1,1) not NULL, " +
                    "[EBIProjectName] nvarchar(100) NULL, " +
                    "[YYYYMM] nvarchar(7) NULL, " +
                    "[ReleaseCnt] int NULL, " +
                    "[ReleaseTimes] int NULL, " +
                    "[TestCaseCnt] int NULL, " +
                    "[ChangeSetCnt] int NULL, " +
                    "[FixedBug] int NULL, " +
                    "[ChangeRequest] int NULL, " +
                    "[Feature] int NULL, " +
                    "[Issue] int NULL, " +
                    "[Task] int NULL, " +
                    "[UserStory] int NULL, " +
                    "[Action] nvarchar(50) NULL, " +
                    "[LastUpdateDT] datetime2 NULL); " + Environment.NewLine +

            "if not exists(select 1 from sys.indexes where object_id=OBJECT_ID('" + setting.StrSchema + "." + ReleaseInfoTableName + "') and name='PK_" + ReleaseInfoTableName + "_ID')" + Environment.NewLine +
            "create clustered index PK_" + ReleaseInfoTableName + "_ID ON " + setting.StrSchema + "." + ReleaseInfoTableName + "(ID);" + Environment.NewLine;
            try
            {
                DBHelper.ExecuteNonQuery(setting.TargetConnectionString, CommandType.Text, sql, null);
                return true;
            }
            catch (Exception ex)
            {
                IOHelper.Log("CreateReleaseInfoAzure ====> " + ex.ToString());
                throw ex;
            }
        }

        private bool CreateReleaseDetailInfoAzure()
        {
            // Release Info
            string sql =
                    "IF OBJECT_ID('" + setting.StrSchema + "." + ReleaseDetailInfoTableName + "') IS NULL " +
                    "CREATE TABLE " + setting.StrSchema + "." + ReleaseDetailInfoTableName + "( " +
                    "[ID] int identity(1,1) not NULL, " +
                    "[EBIProjectName] nvarchar(100) NULL, " +
                    "[YYYYMMDD] nvarchar(20) NULL, " +
                    "[ReleaseCnt] int NULL, " +
                    "[ReleaseTimes] int NULL, " +
                    "[TestCaseCnt] int NULL, " +
                    "[ChangeSetCnt] int NULL, " +
                    "[FixedBug] int NULL, " +
                    "[ChangeRequest] int NULL, " +
                    "[Feature] int NULL, " +
                    "[Issue] int NULL, " +
                    "[Task] int NULL, " +
                    "[UserStory] int NULL); " + Environment.NewLine +

            "if not exists(select 1 from sys.indexes where object_id=OBJECT_ID('" + setting.StrSchema + "." + ReleaseDetailInfoTableName + "') and name='PK_" + ReleaseDetailInfoTableName + "_ID')" + Environment.NewLine +
            "create clustered index PK_" + ReleaseDetailInfoTableName + "_ID ON " + setting.StrSchema + "." + ReleaseDetailInfoTableName + "(ID);" + Environment.NewLine;
            try
            {
                DBHelper.ExecuteNonQuery(setting.TargetConnectionString, CommandType.Text, sql, null);
                return true;
            }
            catch (Exception ex)
            {
                IOHelper.Log("CreateReleaseDetailInfoAzure ====> " + ex.ToString());
                throw ex;
            }
        }


        private bool CreateTestCaseInCIAzure()
        {
            // TestCaseInCI
            string sql =
                    "IF OBJECT_ID('" + setting.StrSchema + "." + TestCaseInCITableName + "') IS NULL " +
                    "CREATE TABLE " + setting.StrSchema + "." + TestCaseInCITableName + "( " +
                    "[ID] int identity(1,1) not NULL, " +
                    "[EBIProjectName] nvarchar(100) NULL, " +
                    "[ReleasedTestCaseCnt] INT NULL, " +
                    "[TestCaseInCI] int NULL, " +
                    "[TestCase_Auto_CI] int NULL, " +
                    "[TestCase_Manual_CI] int NULL, " +
                    "[UTCase_CI] int NULL, " + 
                    "[Action] nvarchar(50) NULL, " +
                    "[LastUpdateDT] datetime2 NULL); " + Environment.NewLine +

                    "if not exists(select 1 from sys.indexes where object_id=OBJECT_ID('" + setting.StrSchema + "." + TestCaseInCITableName + "') and name='PK_" + TestCaseInCITableName + "_ID')" + Environment.NewLine +
            "create clustered index PK_" + TestCaseInCITableName + "_ID ON " + setting.StrSchema + "." + TestCaseInCITableName + "(ID);" + Environment.NewLine;
            try
            {
                DBHelper.ExecuteNonQuery(setting.TargetConnectionString, CommandType.Text, sql, null);
                return true;
            }
            catch (Exception ex)
            {
                IOHelper.Log("CreateTestCaseInCIAzure ====> " + ex.ToString());
                throw ex;
            }
        }

        private bool CreateGlobalTestCasePassRateAzure()
        {
            // GlobalTestCasePassRate
            string sql =
                    "IF OBJECT_ID('" + setting.StrSchema + "." + GlobalTestCasePassRateTableName + "') IS NULL " +
                    "CREATE TABLE " + setting.StrSchema + "." + GlobalTestCasePassRateTableName + "( " +
                    "[ID] int identity(1,1) not NULL, " +
                    "[Financialyear] nvarchar(100) NULL, " +
                    "[ProjectName] nvarchar(100) NULL, " +
                    "[PlanName] nvarchar(100) NULL, " +
                    "[Passed] INT NULL, " +
                    "[Failed] int NULL, " +
                    "[NotExecuted] int NULL, " +
                    "[Total] int NULL, " +
                    "[PassPercent] varchar(100) NULL); " + Environment.NewLine +

            "if not exists(select 1 from sys.indexes where object_id=OBJECT_ID('" + setting.StrSchema + "." + GlobalTestCasePassRateTableName + "') and name='PK_" + GlobalTestCasePassRateTableName + "_ID')" + Environment.NewLine +
            "create clustered index PK_" + GlobalTestCasePassRateTableName + "_ID ON " + setting.StrSchema + "." + GlobalTestCasePassRateTableName + "(ID);" + Environment.NewLine;
            try
            {
                DBHelper.ExecuteNonQuery(setting.TargetConnectionString, CommandType.Text, sql, null);
                return true;
            }
            catch (Exception ex)
            {
                IOHelper.Log("CreateGlobalTestCasePassRateAzure ====> " + ex.ToString());
                throw ex;
            }
        }

        private bool CreateGlobalTestCaseAutomationAzure()
        {
            // GlobalTestCaseAutomation
            string sql =
                    "IF OBJECT_ID('" + setting.StrSchema + "." + GlobalTestCaseAutomationTableName + "') IS NULL " +
                    "CREATE TABLE " + setting.StrSchema + "." + GlobalTestCaseAutomationTableName + "( " +
                    "[ID] int identity(1,1) not NULL, " +
                    "[Financialyear] nvarchar(100) NULL, " +
                    "[Project] nvarchar(100) NULL, " +
                    "[Automated] INT NULL, " +
                    "[HaveAutomationValue] int NULL, " +
                    "[NotAutomationValue] int NULL, " +
                    "[AutomatedOfTotal] nvarchar(100) NULL, " +
                    "[AutomatedOfAutomatable] nvarchar(100) NULL); " + Environment.NewLine +

            "if not exists(select 1 from sys.indexes where object_id=OBJECT_ID('" + setting.StrSchema + "." + GlobalTestCaseAutomationTableName + "') and name='PK_" + GlobalTestCaseAutomationTableName + "_ID')" + Environment.NewLine +
            "create clustered index PK_" + GlobalTestCaseAutomationTableName + "_ID ON " + setting.StrSchema + "." + GlobalTestCaseAutomationTableName + "(ID);" + Environment.NewLine;
            try
            {
                DBHelper.ExecuteNonQuery(setting.TargetConnectionString, CommandType.Text, sql, null);
                return true;
            }
            catch (Exception ex)
            {
                IOHelper.Log("CreateGlobalTestCaseAutomationAzure ====> " + ex.ToString());
                throw ex;
            }
        }

        private bool CreateGlobalDREAzure()
        {
            // GlobalDRE
            string sql =
                    "IF OBJECT_ID('" + setting.StrSchema + "." + GlobalDRETableName + "') IS NULL " +
                    "CREATE TABLE " + setting.StrSchema + "." + GlobalDRETableName + "( " +
                    "[ID] int identity(1,1) not NULL, " +
                    "[Financialyear] nvarchar(100) NULL, " +
                    "[Project] nvarchar(100) NULL, " +
                    "[SITBugs] INT NULL, " +
                    "[ProductionUATBUGS] int NULL, " +
                    "[OtherBugs] int NULL, " +
                    "[DRE] nvarchar(100) NULL); " + Environment.NewLine +

            "if not exists(select 1 from sys.indexes where object_id=OBJECT_ID('" + setting.StrSchema + "." + GlobalDRETableName + "') and name='PK_" + GlobalDRETableName + "_ID')" + Environment.NewLine +
            "create clustered index PK_" + GlobalDRETableName + "_ID ON " + setting.StrSchema + "." + GlobalDRETableName + "(ID);" + Environment.NewLine;
            try
            {
                DBHelper.ExecuteNonQuery(setting.TargetConnectionString, CommandType.Text, sql, null);
                return true;
            }
            catch (Exception ex)
            {
                IOHelper.Log("CreateGlobalDREAzure ====> " + ex.ToString());
                throw ex;
            }
        }

        public void CreateTableAzure()
        {
            if (!CreateETLHistoryAzure())
            {
                IOHelper.Log("CreateETLHistoryAzure fail.");
                return;
            }

            
            if (!CreateBugListAzure())
            {
                IOHelper.Log("CreateTestCaseListAzure fail.");
                return;
            }

            //if (!CreateTestCaseListAzure())
            //{
            //    IOHelper.Log("CreateTestCaseListAzure fail.");
            //    return;
            //}

            if (!CreateNumOfCreatedTCAzure())
            {
                IOHelper.Log("CreateNumOfCreatedTCAzure fail.");
                return;
            }

            if (!CreateNumOfExecutedTCAzure())
            {
                IOHelper.Log("CreateNumOfExecutedTCAzure fail.");
                return;
            }

            if (!CreateTestAutomationAzure())
            {
                IOHelper.Log("CreateTestAutomationAzure fail.");
                return;
            }

            if (!CreateReleaseInfoAzure())
            {
                IOHelper.Log("CreateReleaseInfoAzure fail.");
                return;
            }

            if (!CreateReleaseDetailInfoAzure())
            {
                IOHelper.Log("CreateReleaseDetailInfoAzure fail.");
                return;
            }

            if (!CreateTestCaseInCIAzure())
            {
                IOHelper.Log("CreateTestCaseInCIAzure fail.");
                return;
            }

            if (!CreateGlobalTestCasePassRateAzure())
            {
                IOHelper.Log("CreateGlobalTestCasePassRateAzure fail.");
                return;
            }

            if (!CreateGlobalTestCaseAutomationAzure())
            {
                IOHelper.Log("CreateGlobalTestCaseAutomationAzure fail.");
                return;
            }
            
             if (!CreateGlobalDREAzure())
            {
                IOHelper.Log("CreateGlobalDREAzure fail.");
                return;
            }
        }

        private DataSet GetSourceAzureData(string str)
        {
            DataSet ds = new DataSet();

            SqlDataAdapter da = new SqlDataAdapter(str, setting.SourceConnectionString);
            da.Fill(ds);

            return ds;
        }

        public void LoadETLHistoryToAzure()
        {
            System.Console.WriteLine("This is {0} start. id : {1}",Thread.CurrentThread.Name,Thread.CurrentThread.ManagedThreadId);
            //truncate table 
            DBHelper.ExecuteNonQuery(setting.TargetConnectionString, CommandType.Text, "truncate table [" + setting.StrDB + "].[" + setting.StrSchema + "].[" + ETLHistoryTableName + "]", null);
            string sql = "";

            //Fill tblBugList
            var strList = "select * from tbletlhistory";
            DataSet dsList = GetSourceAzureData(strList);

            int rowCount = dsList.Tables[0].Rows.Count;

            int i = 0;
            while (i < rowCount)
            {
                sql += "insert into " + setting.StrSchema + "." + ETLHistoryTableName + " (Id,TableName,LastUpdateDT)";

                for (int m = 0; m < 100; m++)
                {
                    DataRow row = dsList.Tables[0].Rows[i];
                    string ID = "select '" + Convert.ToInt32(row["ID"]).ToString() + "', ";
                    string TableName = "'" + row["TableName"].ToString() + "', ";
                    string LastUpdateDT = "'" + Convert.ToDateTime(row["LastUpdateDT"]).ToString() + "'    union all";

                    sql += ID + TableName + LastUpdateDT + Environment.NewLine;
                    i++;
                    if (i == rowCount)
                        break;
                }

                sql = sql.Substring(0, sql.Length - 12);
                try
                {
                    DBHelper.ExecuteNonQuery(setting.TargetConnectionString, CommandType.Text, sql, null);
                    sql = "";
                }
                catch (Exception ex)
                {
                    IOHelper.Log("LoadETLHistoryToAzure ====> " + ex.ToString());
                    throw ex;
                }
            }
            System.Console.WriteLine("This is {0} completed. id : {1}", Thread.CurrentThread.Name, Thread.CurrentThread.ManagedThreadId);
        }

        public void LoadBugListToAzure()
        {
            System.Console.WriteLine("This is {0} start. id : {1}", Thread.CurrentThread.Name, Thread.CurrentThread.ManagedThreadId);
            //truncate table 
            DBHelper.ExecuteNonQuery(setting.TargetConnectionString, CommandType.Text, "truncate table [" + setting.StrDB + "].[" + setting.StrSchema + "].[" + BugListTableName + "]", null);
            string sql = "";

            //Fill tblBugList
            var strList = "select * from tblbuglist where CreatedDate>'2013-07-01'";
            DataSet dsList = GetSourceAzureData(strList);

            int rowCount = dsList.Tables[0].Rows.Count;

            int i = 0;
            while (i < rowCount)
            {
                sql += "insert into " + setting.StrSchema + "." + BugListTableName + " (Id,ProjectName,ItemID, AssignedTo, State, Severity, Priority, FoundInEnvironment, CreatedDate, ResolvedDate, ClosedDate, ProjectFriendName, BugType, CreatedBy, ResolvedBy)";

                for (int m = 0; m < 100; m++)
                {
                    DataRow row = dsList.Tables[0].Rows[i];
                    string ID = "select '" + Convert.ToInt32(row["ID"]).ToString() + "', ";
                    string ProjectName = "'" + row["ProjectName"].ToString() + "', ";
                    string ItemID = "'" + Convert.ToInt32(row["ItemID"]).ToString() + "', ";
                    string AssignedTo = "'" + row["AssignedTo"].ToString() + "', ";
                    string State = "'" + row["State"].ToString() + "', ";
                    string Severity = row["Severity"] == DBNull.Value ? "NULL, " : "'" + row["Severity"].ToString() + "', ";
                    string Priority = row["Priority"] == DBNull.Value ? "NULL, " : "'" + row["Priority"].ToString() + "', ";
                    string FoundInEnvironment = row["FoundInEnvironment"] == DBNull.Value ? "NULL, " : "'" + row["FoundInEnvironment"].ToString() + "', ";
                    string CreatedDate = row["CreatedDate"] == DBNull.Value ? "NULL, " : "'" + Convert.ToDateTime(row["CreatedDate"]).ToString() + "', ";
                    string ResolvedDate = row["ResolvedDate"] == DBNull.Value ? "NULL, " : "'" + Convert.ToDateTime(row["ResolvedDate"]).ToString() + "', ";
                    string ClosedDate = row["ClosedDate"] == DBNull.Value ? "NULL, " : "'" + Convert.ToDateTime(row["ClosedDate"]).ToString() + "', ";
                    string ProjectFriendName = row["ProjectFriendName"] == DBNull.Value ? "NULL, " : "'" + row["ProjectFriendName"].ToString() + "', ";
                    string BugType = row["BugType"] == DBNull.Value ? "NULL, " : "'" + row["BugType"].ToString() + "', ";
                    string CreatedBy = row["CreatedBy"] == DBNull.Value ? "NULL, " : "'" + row["CreatedBy"].ToString() + "', ";
                    string ResolvedBy = row["ResolvedBy"] == DBNull.Value ? "NULL   union all " : "'" + row["ResolvedBy"].ToString() + "'   union all";

                    sql += ID + ProjectName + ItemID + AssignedTo + State + Severity + Priority + FoundInEnvironment + CreatedDate + ResolvedDate + ClosedDate + ProjectFriendName + BugType + CreatedBy + ResolvedBy + Environment.NewLine;
                    i++;
                    if (i == rowCount)
                        break;
                }

                sql = sql.Substring(0, sql.Length - 12);
                try
                {
                    DBHelper.ExecuteNonQuery(setting.TargetConnectionString, CommandType.Text, sql, null);
                    sql = "";
                }
                catch (Exception ex)
                {
                    IOHelper.Log("LoadBugListToAzure ====> " + ex.ToString());
                    throw ex;
                }
            }
            System.Console.WriteLine("This is {0} completed. id : {1}", Thread.CurrentThread.Name, Thread.CurrentThread.ManagedThreadId);
        }

        public void LoadTestCaseListToAzure()
        {
            System.Console.WriteLine("This is {0} start. id : {1}", Thread.CurrentThread.Name, Thread.CurrentThread.ManagedThreadId);
            //truncate table 
            DBHelper.ExecuteNonQuery(setting.TargetConnectionString, CommandType.Text, "truncate table [" + setting.StrDB + "].[" + setting.StrSchema + "].[" + TestCaseListTableName + "]", null);
            string sql = "";

            //Fill tblTestCaseList
            var strList = "select * from tblTestCaseList";
            DataSet dsList = GetSourceAzureData(strList);

            int rowCount = dsList.Tables[0].Rows.Count;

            int i = 0;
            while (i < rowCount)
            {
                sql += "insert into " + setting.StrSchema + "." + TestCaseListTableName + " (ID, ProjectName,ItemID,  AreaPath, IterationPath, State, Priority, AutomationStatus, AssignedTo, CreatedBy, CreatedDate, ClosedBy, Class, ClosedDate, Frequency, ProjectFriendName)";

                for (int m = 0; m < 100; m++)
                {
                    DataRow row = dsList.Tables[0].Rows[i];
                    string ID = "select '" + Convert.ToInt32(row["ID"]).ToString() + "', ";
                    string ProjectName = "'" + row["ProjectName"].ToString() + "', ";
                    string ItemID = "'" + Convert.ToInt32(row["ItemID"]).ToString() + "', ";
                    string AreaPath = "'" + row["AreaPath"].ToString() + "', ";
                    string IterationPath = "'" + row["IterationPath"].ToString() + "', ";
                    string State = row["State"] == DBNull.Value ? "NULL, " : "'" + row["State"].ToString() + "', ";
                    string Priority = row["Priority"] == DBNull.Value ? "NULL, " : "'" + row["Priority"].ToString() + "', ";
                    string AutomationStatus = row["AutomationStatus"] == DBNull.Value ? "NULL, " : "'" + row["AutomationStatus"].ToString() + "', ";
                    string AssignedTo = row["AssignedTo"] == DBNull.Value ? "NULL, " : "'" + row["AssignedTo"].ToString() + "', ";
                    string CreatedBy = row["CreatedBy"] == DBNull.Value ? "NULL, " : "'" + row["CreatedBy"].ToString() + "', ";
                    string CreatedDate = row["CreatedDate"] == DBNull.Value ? "NULL, " : "'" + Convert.ToDateTime(row["CreatedDate"]).ToString() + "', ";
                    string ClosedBy = row["ClosedBy"] == DBNull.Value ? "NULL, " : "'" + row["ClosedBy"].ToString() + "', ";
                    string Class = row["Class"] == DBNull.Value ? "NULL, " : "'" + row["Class"].ToString() + "', ";
                    string ClosedDate = row["ClosedDate"] == DBNull.Value ? "NULL, " : "'" + Convert.ToDateTime(row["ClosedDate"]).ToString() + "', ";
                    string Frequency = row["Frequency"] == DBNull.Value ? "NULL, " : "'" + row["Frequency"].ToString() + "', ";
                    string ProjectFriendName = row["ProjectFriendName"] == DBNull.Value ? "NULL   union all " : "'" + row["ProjectFriendName"].ToString() + "'   union all";

                    sql += ID + ProjectName + ItemID + AreaPath + IterationPath + State + Priority + AutomationStatus + AssignedTo + CreatedBy + CreatedDate + ClosedBy + Class + ClosedDate + Frequency + ProjectFriendName+ Environment.NewLine;
                    i++;
                    if (i == rowCount)
                        break;
                }

                sql = sql.Substring(0, sql.Length - 12);
                try
                {
                    DBHelper.ExecuteNonQuery(setting.TargetConnectionString, CommandType.Text, sql, null);
                    sql = "";
                }
                catch (Exception ex)
                {
                    IOHelper.Log("LoadTestCaseListToAzure ====> " + ex.ToString());
                    throw ex;
                }
                
            }
            System.Console.WriteLine("This is {0} completed. id : {1}", Thread.CurrentThread.Name, Thread.CurrentThread.ManagedThreadId);
        }

        public void LoadNumOfCreatedTCToAzure()
        {
            System.Console.WriteLine("This is {0} start. id : {1}", Thread.CurrentThread.Name, Thread.CurrentThread.ManagedThreadId);
            //truncate table 
            DBHelper.ExecuteNonQuery(setting.TargetConnectionString, CommandType.Text, "truncate table [" + setting.StrDB + "].[" + setting.StrSchema + "].[" + NumOfCreatedTCATableName + "]", null);
            string sql = "";

            //Fill NumOfCreatedTC
            var strList = "select * from [dbo].[vwNumOfCreatedTC]";
            DataSet dsList = GetSourceAzureData(strList);

            int rowCount = dsList.Tables[0].Rows.Count;

            int i = 0;
            while (i < rowCount)
            {
                sql += "insert into " + setting.StrSchema + "." + NumOfCreatedTCATableName + " (Application, CreatedDate, NumCreatedTC)";

                for (int m = 0; m < 100; m++)
                {
                    DataRow row = dsList.Tables[0].Rows[i];
                    string Application = "select '" + row["Application"].ToString() + "', ";
                    string CreatedDate = "'" + row["CreatedDate"].ToString() + "', ";
                    string NumCreatedTC = "'" + Convert.ToInt32(row["NumCreatedTC"]).ToString() + "'   union all";

                    sql += Application + CreatedDate + NumCreatedTC + Environment.NewLine;
                    i++;
                    if (i == rowCount)
                        break;
                }

                sql = sql.Substring(0, sql.Length - 12);
                try
                {
                    DBHelper.ExecuteNonQuery(setting.TargetConnectionString, CommandType.Text, sql, null);
                    sql = "";
                }
                catch (Exception ex)
                {
                    IOHelper.Log("LoadNumOfCreatedTCToAzure ====> " + ex.ToString());
                    throw ex;
                }
            }
            System.Console.WriteLine("This is {0} completed. id : {1}", Thread.CurrentThread.Name, Thread.CurrentThread.ManagedThreadId);
        }

        public void LoadNumOfExecutedTCToAzure()
        {
            System.Console.WriteLine("This is {0} start. id : {1}", Thread.CurrentThread.Name, Thread.CurrentThread.ManagedThreadId);
            //truncate table 
            DBHelper.ExecuteNonQuery(setting.TargetConnectionString, CommandType.Text, "truncate table [" + setting.StrDB + "].[" + setting.StrSchema + "].[" + NumOfExecutedTCTableName + "]", null);
            string sql = "";

            //Fill NumOfExecutedTC
            var strList = "select * from [dbo].[vwNumOfExecutedTC]";
            DataSet dsList = GetSourceAzureData(strList);

            int rowCount = dsList.Tables[0].Rows.Count;

            int i = 0;
            while (i < rowCount)
            {
                sql += "insert into " + setting.StrSchema + "." + NumOfExecutedTCTableName + " (Application, ExecutedStartDt, NumExecutedTC, NumPassed, NumFailed)";

                for (int m = 0; m < 100; m++)
                {
                    DataRow row = dsList.Tables[0].Rows[i];
                    string Application = "select '" + row["Application"].ToString() + "', ";
                    string ExecutedStartDt = "'" + row["ExecutedStartDt"].ToString() + "', ";
                    string NumExecutedTC = "'" + Convert.ToInt32(row["NumExecutedTC"]).ToString() + "', ";
                    string NumPassed = "'" + Convert.ToInt32(row["NumPassed"]).ToString() + "', ";
                    string NumFailed = "'" + Convert.ToInt32(row["NumFailed"]).ToString() + "'   union all";

                    sql += Application + ExecutedStartDt + NumExecutedTC + NumPassed + NumFailed + Environment.NewLine;
                    i++;
                    if (i == rowCount)
                        break;
                }

                sql = sql.Substring(0, sql.Length - 12);
                try
                {
                    DBHelper.ExecuteNonQuery(setting.TargetConnectionString, CommandType.Text, sql, null);
                    sql = "";
                }
                catch (Exception ex)
                {
                    IOHelper.Log("LoadNumOfExecutedTCToAzure ====> " + ex.ToString());
                    throw ex;
                }
            }
            System.Console.WriteLine("This is {0} completed. id : {1}", Thread.CurrentThread.Name, Thread.CurrentThread.ManagedThreadId);
        }

        public void LoadTestAutomationToAzure()
        {
            System.Console.WriteLine("This is {0} start. id : {1}", Thread.CurrentThread.Name, Thread.CurrentThread.ManagedThreadId);
            //truncate table 
            DBHelper.ExecuteNonQuery(setting.TargetConnectionString, CommandType.Text, "truncate table [" + setting.StrDB + "].[" + setting.StrSchema + "].[" + TestAutomationTableName + "]", null);
            string sql = "";

            //Fill TestAutomation
            var strList = "select * from [dbo].[vwTestAutomation]";
            DataSet dsList = GetSourceAzureData(strList);

            int rowCount = dsList.Tables[0].Rows.Count;

            int i = 0;
            while (i < rowCount)
            {
                sql += "insert into " + setting.StrSchema + "." + TestAutomationTableName + " (Application, CreatedDate, TotalTestCases, Automatable, CannotAutomated, AutomatableNotReady, AlreadyAutomated, Gap, RateAlreadyAutomated, RateAutomatable, LinesOfAutomationCode)";

                for (int m = 0; m < 100; m++)
                {
                    DataRow row = dsList.Tables[0].Rows[i];
                    string Application = "select '" + row["Application"].ToString() + "', ";
                    string CreatedDate = "'" + row["CreatedDate"].ToString() + "', ";
                    string TotalTestCases = "'" + Convert.ToInt32(row["TotalTestCases"]).ToString() + "', ";
                    string Automatable = "'" + Convert.ToInt32(row["Automatable"]).ToString() + "', ";
                    string CannotAutomated = "'" + Convert.ToInt32(row["CannotAutomated"]).ToString() + "', ";
                    string AutomatableNotReady = "'" + Convert.ToInt32(row["AutomatableNotReady"]).ToString() + "', ";
                    string AlreadyAutomated = "'" + Convert.ToInt32(row["AlreadyAutomated"]).ToString() + "', ";
                    string Gap = "'" + Convert.ToInt32(row["Gap"]).ToString() + "', ";
                    string RateAlreadyAutomated = "'" + Convert.ToInt32(row["RateAlreadyAutomated"]).ToString() + "', ";
                    string RateAutomatable = "'" + Convert.ToInt32(row["RateAutomatable"]).ToString() + "', ";
                    string LinesOfAutomationCode = "'" + Convert.ToInt32(row["LinesOfAutomationCode"]).ToString() + "'   union all";

                    sql += Application + CreatedDate + TotalTestCases + Automatable + CannotAutomated + AutomatableNotReady + AlreadyAutomated + Gap + RateAlreadyAutomated + RateAutomatable + LinesOfAutomationCode+Environment.NewLine;
                    i++;
                    if (i == rowCount)
                        break;
                }

                sql = sql.Substring(0, sql.Length - 12);
                try
                {
                    DBHelper.ExecuteNonQuery(setting.TargetConnectionString, CommandType.Text, sql, null);
                    sql = "";
                }
                catch (Exception ex)
                {
                    IOHelper.Log("LoadTestAutomationToAzure ====> " + ex.ToString());
                    throw ex;
                }
            }
            System.Console.WriteLine("This is {0} completed. id : {1}", Thread.CurrentThread.Name, Thread.CurrentThread.ManagedThreadId);
        }

        public void LoadReleaseInfoToAzure()
        {
            
            //truncate table 
            DBHelper.ExecuteNonQuery(setting.TargetConnectionString, CommandType.Text, "truncate table [" + setting.StrDB + "].[" + setting.StrSchema + "].[" + ReleaseInfoTableName + "]", null);
            string sql = "";

            //Fill ReleaseInfo
            var strList = "select * from [dbo].[vwReleaseInfo]";
            DataSet dsList = GetSourceAzureData(strList);

            int rowCount = dsList.Tables[0].Rows.Count;

            int i = 0;
            while (i < rowCount)
            {
                sql += "insert into " + setting.StrSchema + "." + ReleaseInfoTableName + " (EBIProjectName, YYYYMM, ReleaseCnt, ReleaseTimes, TestCaseCnt,ChangeSetCnt,FixedBug,ChangeRequest,Feature,Issue,Task,UserStory,[Action],LastUpdateDT)";

                for (int m = 0; m < 100; m++)
                {
                    DataRow row = dsList.Tables[0].Rows[i];
                    string EBIProjectName = "select '" + row["EBIProjectName"].ToString() + "', ";
                    string YYYYMM = "'" + row["YYYYMM"].ToString() + "', ";
                    string ReleaseCnt = "'" + Convert.ToInt32(row["ReleaseCnt"]).ToString() + "', ";
                    string ReleaseTimes = "'" + Convert.ToInt32(row["ReleaseTimes"]).ToString() + "', ";
                    string TestCaseCnt = "'" + Convert.ToInt32(row["TestCaseCnt"]).ToString() + "', ";
                    string ChangeSetCnt = "'" + Convert.ToInt32(row["ChangeSetCnt"]).ToString() + "', ";
                    string FixedBug = "'" + Convert.ToInt32(row["FixedBug"]).ToString() + "', ";
                    string ChangeRequest = "'" + Convert.ToInt32(row["ChangeRequest"]).ToString() + "', ";
                    string Feature = "'" + Convert.ToInt32(row["Feature"]).ToString() + "', ";
                    string Issue = "'" + Convert.ToInt32(row["Issue"]).ToString() + "', ";
                    string Task = "'" + Convert.ToInt32(row["Task"]).ToString() + "', ";
                    string UserStory = "'" + Convert.ToInt32(row["UserStory"]).ToString() + "', ";
                    string Action = "'" + row["Action"].ToString() + "', ";
                    string LastUpdateDT = "'" + Convert.ToDateTime(row["LastUpdateDT"]).ToString() + "'   union all";

                    sql += EBIProjectName + YYYYMM + ReleaseCnt + ReleaseTimes + TestCaseCnt + ChangeSetCnt + FixedBug + ChangeRequest + Feature + Issue + Task + UserStory+Action+LastUpdateDT+Environment.NewLine;
                    i++;
                    if (i == rowCount)
                        break;
                }

                sql = sql.Substring(0, sql.Length - 12);
                try
                {
                    DBHelper.ExecuteNonQuery(setting.TargetConnectionString, CommandType.Text, sql, null);
                    sql = "";
                }
                catch (Exception ex)
                {
                    IOHelper.Log("LoadReleaseInfoToAzure ====> " + ex.ToString());
                    throw ex;
                }
            }
        }


        public void LoadReleaseDetailInfoToAzure()
        {

            //truncate table 
            DBHelper.ExecuteNonQuery(setting.TargetConnectionString, CommandType.Text, "truncate table [" + setting.StrDB + "].[" + setting.StrSchema + "].[" + ReleaseDetailInfoTableName + "]", null);
            string sql = "";

            //Fill ReleaseDetailInfo
            var strList = "select * from [dbo].[vwReleaseDetailInfo]";
            DataSet dsList = GetSourceAzureData(strList);

            int rowCount = dsList.Tables[0].Rows.Count;

            int i = 0;
            while (i < rowCount)
            {
                sql += "insert into " + setting.StrSchema + "." + ReleaseDetailInfoTableName + " (EBIProjectName, YYYYMMDD, ReleaseCnt, ReleaseTimes, TestCaseCnt,ChangeSetCnt,FixedBug,ChangeRequest,Feature,Issue,Task,UserStory)";

                for (int m = 0; m < 100; m++)
                {
                    DataRow row = dsList.Tables[0].Rows[i];
                    string EBIProjectName = "select '" + row["EBIProjectName"].ToString() + "', ";
                    string YYYYMMDD = "'" + row["YYYYMMDD"].ToString() + "', ";
                    string ReleaseCnt = "'" + Convert.ToInt32(row["ReleaseCnt"]).ToString() + "', ";
                    string ReleaseTimes = "'" + Convert.ToInt32(row["ReleaseTimes"]).ToString() + "', ";
                    string TestCaseCnt = "'" + Convert.ToInt32(row["TestCaseCnt"]).ToString() + "', ";
                    string ChangeSetCnt = "'" + Convert.ToInt32(row["ChangeSetCnt"]).ToString() + "', ";
                    string FixedBug = "'" + Convert.ToInt32(row["FixedBug"]).ToString() + "', ";
                    string ChangeRequest = "'" + Convert.ToInt32(row["ChangeRequest"]).ToString() + "', ";
                    string Feature = "'" + Convert.ToInt32(row["Feature"]).ToString() + "', ";
                    string Issue = "'" + Convert.ToInt32(row["Issue"]).ToString() + "', ";
                    string Task = "'" + Convert.ToInt32(row["Task"]).ToString() + "', ";
                    string UserStory = "'" + Convert.ToInt32(row["UserStory"]).ToString() + "'   union all";

                    sql += EBIProjectName + YYYYMMDD + ReleaseCnt + ReleaseTimes + TestCaseCnt + ChangeSetCnt + FixedBug + ChangeRequest + Feature + Issue + Task + UserStory + Environment.NewLine;
                    i++;
                    if (i == rowCount)
                        break;
                }

                sql = sql.Substring(0, sql.Length - 12);
                try
                {
                    DBHelper.ExecuteNonQuery(setting.TargetConnectionString, CommandType.Text, sql, null);
                    sql = "";
                }
                catch (Exception ex)
                {
                    IOHelper.Log("LoadReleaseDetailInfoToAzure ====> " + ex.ToString());
                    throw ex;
                }
            }
        }

        public void LoadTestCaseInCIAzure()
        {

            //truncate table 
            DBHelper.ExecuteNonQuery(setting.TargetConnectionString, CommandType.Text, "truncate table [" + setting.StrDB + "].[" + setting.StrSchema + "].[" + TestCaseInCITableName + "]", null);
            string sql = "";

            //Fill TestCaseInCI
            var strList = "select * from [dbo].[vwTestCaseInCI]";
            DataSet dsList = GetSourceAzureData(strList);

            int rowCount = dsList.Tables[0].Rows.Count;

            int i = 0;
            while (i < rowCount)
            {
                sql += "insert into " + setting.StrSchema + "." + TestCaseInCITableName + " (EBIProjectName, ReleasedTestCaseCnt, TestCaseInCI, TestCase_Auto_CI, TestCase_Manual_CI,UTCase_CI, action, lastUpdateDT)";

                for (int m = 0; m < 100; m++)
                {
                    DataRow row = dsList.Tables[0].Rows[i];
                    string EBIProjectName = "select '" + row["EBIProjectName"].ToString() + "', ";
                    string ReleasedTestCaseCnt = "'" + Convert.ToInt32(row["ReleasedTestCaseCnt"]).ToString() + "', ";
                    string TestCaseInCI = "'" + Convert.ToInt32(row["TestCaseInCI"]).ToString() + "', ";
                    string TestCase_Auto_CI = "'" + Convert.ToInt32(row["TestCase_Auto_CI"]).ToString() + "', ";
                    string TestCase_Manual_CI = "'" + Convert.ToInt32(row["TestCase_Manual_CI"]).ToString() + "', ";
                    string UTCase_CI = "'" + Convert.ToInt32(row["UTCase_CI"]).ToString() + "', ";
                    string Action = "'" + row["Action"].ToString() + "', ";
                    string LastUpdateDT = "'" + Convert.ToDateTime(row["LastUpdateDT"]).ToString() + "'   union all";

                    sql += EBIProjectName + ReleasedTestCaseCnt + TestCaseInCI + TestCase_Auto_CI + TestCase_Manual_CI + UTCase_CI + Action + LastUpdateDT + Environment.NewLine;
                    i++;
                    if (i == rowCount)
                        break;
                }

                sql = sql.Substring(0, sql.Length - 12);
                try
                {
                    DBHelper.ExecuteNonQuery(setting.TargetConnectionString, CommandType.Text, sql, null);
                    sql = "";
                }
                catch (Exception ex)
                {
                    IOHelper.Log("LoadTestCaseInCIAzure ====> " + ex.ToString());
                    throw ex;
                }
            }
        }

        public void LoadGlobalTestCasePassRateAzure()
        {

            //truncate table 
            DBHelper.ExecuteNonQuery(setting.TargetConnectionString, CommandType.Text, "truncate table [" + setting.StrDB + "].[" + setting.StrSchema + "].[" + GlobalTestCasePassRateTableName + "]", null);
            string sql = "";

            //Fill GlobalTestCasePassRate
            var strList = "select * from [dbo].[vwGlobalTestCasePassRate]";
            DataSet dsList = GetSourceAzureData(strList);

            int rowCount = dsList.Tables[0].Rows.Count;

            int i = 0;
            while (i < rowCount)
            {
                sql += "insert into " + setting.StrSchema + "." + GlobalTestCasePassRateTableName + " (Financialyear,ProjectName,PlanName, Passed, Failed, NotExecuted, Total,PassPercent)";

                for (int m = 0; m < 100; m++)
                {
                    DataRow row = dsList.Tables[0].Rows[i];
                    string Financialyear = " select '" + row["Financialyear"].ToString() + "', ";
                    string ProjectName = "'" + row["ProjectName"].ToString() + "', ";
                    string PlanName = "'" + row["PlanName"].ToString() + "', ";
                    string Passed = "'" + Convert.ToInt32(row["Passed"]).ToString() + "', ";
                    string Failed = "'" + Convert.ToInt32(row["Failed"]).ToString() + "', ";
                    string NotExecuted = "'" + Convert.ToInt32(row["NotExecuted"]).ToString() + "', ";
                    string Total = "'" + Convert.ToInt32(row["Total"]).ToString() + "', ";
                    string PassPercent = "'" + row["PassPercent"].ToString() + "'   union all";

                    sql += Financialyear+ ProjectName + PlanName + Passed + Failed + NotExecuted + Total + PassPercent + Environment.NewLine;
                    i++;
                    if (i == rowCount)
                        break;
                }

                sql = sql.Substring(0, sql.Length - 12);
                try
                {
                    DBHelper.ExecuteNonQuery(setting.TargetConnectionString, CommandType.Text, sql, null);
                    sql = "";
                }
                catch (Exception ex)
                {
                    IOHelper.Log("LoadGlobalTestCasePassRateAzure ====> " + ex.ToString());
                    throw ex;
                }
            }
        }


        public void LoadGlobalTestCaseAutomationAzure()
        {

            //truncate table 
            DBHelper.ExecuteNonQuery(setting.TargetConnectionString, CommandType.Text, "truncate table [" + setting.StrDB + "].[" + setting.StrSchema + "].[" + GlobalTestCaseAutomationTableName + "]", null);
            string sql = "";

            //Fill GlobalTestCasePassRate
            var strList = "select * from [dbo].[vwGlobalTestCaseAutomation]";
            DataSet dsList = GetSourceAzureData(strList);

            int rowCount = dsList.Tables[0].Rows.Count;

            int i = 0;
            while (i < rowCount)
            {
                sql += "insert into " + setting.StrSchema + "." + GlobalTestCaseAutomationTableName + " (Financialyear,Project,Automated, [HaveAutomationValue], [NotAutomationValue], [AutomatedOfTotal], [AutomatedOfAutomatable])";

                for (int m = 0; m < 100; m++)
                {
                    DataRow row = dsList.Tables[0].Rows[i];
                    string Financialyear = " select '" + row["Financialyear"].ToString() + "', ";
                    string Project = "'" + row["Project"].ToString() + "', ";
                    string Automated = "'" + Convert.ToInt32(row["Automated"]).ToString() + "', ";
                    string HaveAutomationValue = "'" + Convert.ToInt32(row["Have Automation Value"]).ToString() + "', ";
                    string NotAutomationValue = "'" + Convert.ToInt32(row["Not Automation Value"]).ToString() + "', ";
                    string AutomatedOfTotal = "'" + row["Automated % of Total"].ToString() + "', ";
                    string AutomatedOfAutomatable = "'" + row["Automated % of Automatable"].ToString() + "'   union all";

                    sql += Financialyear+ Project + Automated + HaveAutomationValue + NotAutomationValue + AutomatedOfTotal + AutomatedOfAutomatable + Environment.NewLine;
                    i++;
                    if (i == rowCount)
                        break;
                }

                sql = sql.Substring(0, sql.Length - 12);
                try
                {
                    DBHelper.ExecuteNonQuery(setting.TargetConnectionString, CommandType.Text, sql, null);
                    sql = "";
                }
                catch (Exception ex)
                {
                    IOHelper.Log("LoadGlobalTestCaseAutomationAzure ====> " + ex.ToString());
                    throw ex;
                }
            }
        }


        public void LoadGlobalDREAzure()
        {

            //truncate table 
            DBHelper.ExecuteNonQuery(setting.TargetConnectionString, CommandType.Text, "truncate table [" + setting.StrDB + "].[" + setting.StrSchema + "].[" + GlobalDRETableName + "]", null);
            string sql = "";

            //Fill GlobalDRE
            var strList = "select * from [dbo].[vwGlobalDRE]";
            DataSet dsList = GetSourceAzureData(strList);

            int rowCount = dsList.Tables[0].Rows.Count;

            int i = 0;
            while (i < rowCount)
            {
                sql += "insert into " + setting.StrSchema + "." + GlobalDRETableName + " (Project,Financialyear,[SITBugs], [ProductionUATBUGS], [OtherBugs], [DRE])";

                for (int m = 0; m < 100; m++)
                {
                    DataRow row = dsList.Tables[0].Rows[i];
                    string Project = "select '" + row["Project"].ToString() + "', ";
                    string Financialyear = "'" + row["Financialyear"].ToString() + "', ";
                    string SITBugs = "'" + Convert.ToInt32(row["SIT Bugs"]).ToString() + "', ";
                    string ProductionUATBUGS = "'" + Convert.ToInt32(row["Production/UAT BUGS"]).ToString() + "', ";
                    string OtherBugs = "'" + Convert.ToInt32(row["Other Bugs"]).ToString() + "', ";
                    string DRE = "'" + row["DRE"].ToString() + "'   union all";

                    sql += Project + Financialyear + SITBugs + ProductionUATBUGS + OtherBugs + DRE + Environment.NewLine;
                    i++;
                    if (i == rowCount)
                        break;
                }

                sql = sql.Substring(0, sql.Length - 12);
                try
                {
                    DBHelper.ExecuteNonQuery(setting.TargetConnectionString, CommandType.Text, sql, null);
                    sql = "";
                }
                catch (Exception ex)
                {
                    IOHelper.Log("LoadGlobalDREAzure ====> " + ex.ToString());
                    throw ex;
                }
            }
        }
    }
}
